﻿using System;
using System.Data;
using System.Configuration;
using System.Linq;
using System.Web;
using System.Web.Security;
using System.Web.UI;
using System.Web.UI.HtmlControls;
using System.Web.UI.WebControls;
using System.Web.UI.WebControls.WebParts;
using System.Xml.Linq;
using System.Data.Sql;
using System.Data.SqlClient;
using System.Collections.Generic;

namespace LMIS_Application
{
    public class Book
    {
        private int id;
        private String title;
        private String author;
        private int year;
        private int status;

        /* Promoted Fields (needed for data binding) */
        public int BookId
        {
            get
            {
                return getID();
            }
        }

        public String Title
        {
            get
            {
                return getTitle();
            }
            set
            {
                setTitle(value);
            }
        }

        /* End Promoted Fields */
        
        public int getID()
        {
            return id;
        }

        public String getTitle()
        {
            return title;
        }
        public void setTitle(String _title)
        {
            title = _title;
        }

        public String getAuthor()
        {
            return author;
        }
        public void setAuthor(String _author)
        {
            author = _author;
        }

        public int getYear()
        {
            return year;
        }
        public void setYear(int _year)
        {
            year = _year;
        }
        public int getStatus()
        {
            return status;
        }
        public void setStatus(int _status)
        {
            status = _status;
        }

        public void loadBook(int bkId){
            //load book details into the class
            string command = "SELECT * from Book WHERE BookId='" + bkId + "'";

            using (SqlConnection conn = (SqlConnection)DbHelper.GetDatabaseConnection())
            {
                conn.Open();
                SqlDataAdapter da = new SqlDataAdapter(command, conn);
                DataSet ds = new DataSet();
                da.Fill(ds, "Book");

                int rowCount = ds.Tables["Book"].Rows.Count;

                if (rowCount > 0)
                {
                    //get the first row
                    DataRow dr = ds.Tables["Book"].Rows[0];
                    this.id = Convert.ToInt32(dr["BookID"]);
                    this.title = (string)dr["Title"];
                    this.author = (string)dr["AuthorName"];
                    this.year = Convert.ToInt32(dr["Year"]);
                    this.status = Convert.ToInt32(dr["Status"]);
                }
                conn.Close();
            }

        }
        public bool addBook(String _title, String _author, int _year)
        {
            title = _title;
            author = _author;
            year = _year;

            //save them to sql
            string command = "INSERT INTO BOOK(Title, AuthorName, Year, Status) " +
                                "VALUES('" + title +"','" + author +"','" + year.ToString() +"','1')";

            SqlConnection conn = (SqlConnection)DbHelper.GetDatabaseConnection();
            SqlCommand sc = new SqlCommand();
            sc.Connection = conn;
            sc.CommandText = command;
            sc.Connection.Open();
            Int32 rowsAffected = -1;
            rowsAffected = (Int32) sc.ExecuteNonQuery();
            sc.Connection.Close();

            if (rowsAffected >= 1)
            {
                command = "SELECT BookID FROM BOOK WHERE Title = '" + title + "' AND AuthorName = '" + author + "' AND Year = '" + year + "'";
                sc.CommandText = command;
                sc.Connection.Open();
                Int32 bookid = (Int32)sc.ExecuteScalar();
                sc.Connection.Close();

                id = bookid;

                return true;
            }
            else
            {
                return false;
            }

        }
        public int delBook(int bkId)
        {

            //check to see if there are any book instances in inventory
            String command = "SELECT * FROM INVENTORY WHERE BookId = '" + bkId + "'";
            SqlConnection conn = (SqlConnection)DbHelper.GetDatabaseConnection();
            SqlCommand sc = new SqlCommand();
            sc.Connection = conn;
            sc.CommandText = command;
            sc.Connection.Open();
            SqlDataReader sdr = sc.ExecuteReader();
            
            //if there are book return 
            //-1 which equals that there are books
            //0 cant delete
            //1 delete sucessful
            if ( sdr.HasRows )
            {
                sc.Connection.Close();
                return -1;
            }
            else
            {
                sc.Connection.Close();

                //see if there is a book
                command = "SELECT * FROM BOOK WHERE BookId = '" + bkId + "'";
                conn = (SqlConnection)DbHelper.GetDatabaseConnection();
                sc = new SqlCommand();
                sc.Connection = conn;
                sc.CommandText = command;
                sc.Connection.Open();
                sdr = sc.ExecuteReader();

                if (!sdr.HasRows)
                {
                    sc.Connection.Close();
                    return 0;
                }

                //delete book
                command = "DELETE FROM BOOK WHERE BookId = '" + bkId + "'";
                conn = (SqlConnection)DbHelper.GetDatabaseConnection();
                sc = new SqlCommand();
                sc.Connection = conn;
                sc.CommandText = command;
                sc.Connection.Open();
                sc.ExecuteNonQuery();
                sc.Connection.Close();

                //check to see if it was deleted
                sc = new SqlCommand();
                sc.Connection = conn;
                sc.CommandText = "SELECT * FROM BOOK WHERE BookId = '" + bkId + "'";
                sc.Connection.Open();
                SqlDataReader sdr_book = sc.ExecuteReader();
                
                //if there are rows then for whatever reason it did not get deleted
                if (!sdr_book.HasRows)
                {
                    sc.Connection.Close();
                    return 1;
                }
                else
                {
                    sc.Connection.Close();
                    return 0;
                }
            }
        }

        public bool ForceDelBook(int bkId)
        {

            //delete all books in inventory for a particular book
            String command = "DELETE FROM Inventory WHERE BookId = '" + bkId + "'";
            SqlConnection conn = (SqlConnection)DbHelper.GetDatabaseConnection();
            SqlCommand sc = new SqlCommand();
            sc.Connection = conn;
            sc.CommandText = command;
            sc.Connection.Open();
            sc.ExecuteNonQuery();
            sc.Connection.Close();

            //delete book from book table
            sc = new SqlCommand();
            sc.Connection = conn;
            sc.CommandText = "DELETE FROM BOOK WHERE BookId = '" + bkId + "'";
            sc.Connection.Open();
            sc.ExecuteNonQuery();
            sc.Connection.Close();

            //check to make sure it was deleted
            sc = new SqlCommand();
            sc.Connection = conn;
            sc.CommandText = "SELECT * FROM BOOK WHERE BookId = '" + bkId + "'";
            sc.Connection.Open();
            SqlDataReader sdr = sc.ExecuteReader();
            
            //if there are rows then for whatever reason it did not get deleted
            if (sdr.HasRows)
            {
                sc.Connection.Close();
                return false;
            }
            else
            {
                sc.Connection.Close();
                return true;
            }
        }

        public static List<Book> GetBookList()
        {
            List<Book> books = new List<Book>();
            string command = "SELECT BookId from Book";

            using (SqlConnection conn = (SqlConnection)DbHelper.GetDatabaseConnection())
            {
                conn.Open();
                SqlDataAdapter da = new SqlDataAdapter(command, conn);
                DataSet ds = new DataSet();
                da.Fill(ds, "Book");

                foreach (DataRow r in ds.Tables["Book"].Rows)
                {
                    int bkId = (int)r["BookId"];
                    Book b = new Book();
                    b.loadBook(bkId);
                    books.Add(b);
                }
            }
            return books;
        }
    }
}